package com.sinog.biz.util;

import com.sinog.biz.util.smlib.Sm4;
import com.sinog.core.domain.PrisonerMain;
import com.sinog.core.domain.PrisonerMainExcelTemp;
import com.sinog.core.domain.PrisonerSr;
import com.sinog.core.domain.PrisonerSrExcelTemp;
import com.sinog.core.domain.PrisonerZn;
import com.sinog.core.domain.PrisonerZnExcelTemp;
import com.sinog.core.domain.StaffInfoManage;
import com.sinog.core.domain.VolunteerManageInfo;
import com.sinog.core.util.StrUtil;
import com.sinog.core.utilbean.Constants;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;

/**
 * @desc Sql工具类
 * @author lzq
 * @date 2020-08-31 4:12 下午
 */
@Slf4j
public final class SqlUtil {

    private static final String COMMON_DATE = "yyyy-MM-dd HH:mm:ss";

    /**
     * 组装sql字符串
     * @param boundSql boundSql
     * @param parameterObject parameterObject
     * @param configuration configuration
     * @return String
     */
    public static String getSql(BoundSql boundSql,Object parameterObject,Configuration configuration) {
        String sql = boundSql.getSql().replaceAll("[\\s]+"," ");
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        if(null != parameterMappings) {
            for(ParameterMapping parameterMapping : parameterMappings) {
                if(parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if(boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if(null == parameterObject) {
                        value = null;
                    } else if(typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else {
                        MetaObject metaObject = configuration.newMetaObject(parameterObject);
                        value = metaObject.getValue(propertyName);
                        if(StrUtil.contains(propertyName,"idNum","znsfId","sfzh","idnumber") && null != value && !"".equals(value.toString())) {
                            if(!Sm4.checkIsE(value.toString())) {
                                value = Sm4.encryptEcb(value.toString());
                            }
                            if(parameterObject instanceof PrisonerMain) {
                                PrisonerMain p = (PrisonerMain)parameterObject;
                                p.setIdNum(value.toString());
                            } else if(parameterObject instanceof StaffInfoManage) {
                                StaffInfoManage p = (StaffInfoManage)parameterObject;
                                p.setSfzh(value.toString());
                            } else if(parameterObject instanceof VolunteerManageInfo) {
                                VolunteerManageInfo p = (VolunteerManageInfo)parameterObject;
                                p.setSfzh(value.toString());
                            } else if(parameterObject instanceof PrisonerZn) {
                                PrisonerZn p = (PrisonerZn)parameterObject;
                                p.setZnsfId(value.toString());
                            } else if(parameterObject instanceof PrisonerSr) {
                                PrisonerSr p = (PrisonerSr)parameterObject;
                                p.setIdnumber(value.toString());
                            } else if(parameterObject instanceof Map) {
                                Map<String,Object> m = (Map<String,Object>)parameterObject;
                                if(m.get("et").toString().startsWith("PrisonerMain")) {
                                    if(!m.get("et").toString().startsWith("PrisonerMainExcelTemp")) {
                                        PrisonerMain mm = (PrisonerMain)m.get("et");
                                        mm.setIdNum(value.toString());
                                    } else {
                                        PrisonerMainExcelTemp mm = (PrisonerMainExcelTemp)m.get("et");
                                        mm.setIdNum(value.toString());
                                    }
                                }
                                if(m.get("et").toString().startsWith("PrisonerZn")) {
                                    if(!m.get("et").toString().startsWith("PrisonerZnExcelTemp")) {
                                        PrisonerZn mm = (PrisonerZn)m.get("et");
                                        mm.setZnsfId(value.toString());
                                    } else {
                                        PrisonerZnExcelTemp mm = (PrisonerZnExcelTemp)m.get("et");
                                        mm.setZnsfId(value.toString());
                                    }
                                }
                                if(m.get("et").toString().startsWith("StaffInfoManage")) {
                                    StaffInfoManage mm = (StaffInfoManage)m.get("et");
                                    mm.setSfzh(value.toString());
                                }
                                if(m.get("et").toString().startsWith("VolunteerManageInfo")) {
                                    VolunteerManageInfo mm = (VolunteerManageInfo)m.get("et");
                                    mm.setSfzh(value.toString());
                                }
                                if(m.get("et").toString().startsWith("PrisonerSr")) {
                                    if(!m.get("et").toString().startsWith("PrisonerSrExcelTemp")) {
                                        PrisonerSr mm = (PrisonerSr)m.get("et");
                                        mm.setIdnumber(value.toString());
                                    } else {
                                        PrisonerSrExcelTemp mm = (PrisonerSrExcelTemp)m.get("et");
                                        mm.setIdnumber(value.toString());
                                    }
                                }
                            }
                        }
                    }
                    //将?替换为参数
                    sql = replacePlaceholder(sql,value);
                }
            }
        }
        return sql;
    }

    /**
     * 将?替换为参数
     * @param sql sql
     * @param propertyValue propertyValue
     * @return String
     */
    private static String replacePlaceholder(String sql,Object propertyValue) {
        String result;
        if(null != propertyValue) {
            if(propertyValue instanceof String) {
                result = "'" + propertyValue + "'";
            } else if(propertyValue instanceof Date) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat(COMMON_DATE);
                result = "'" + simpleDateFormat.format(propertyValue) + "'";
            } else {
                result = propertyValue.toString();
            }
        } else {
            result = "null";
        }
        return sql.replaceFirst("\\?",Matcher.quoteReplacement(result));
    }

    /**
     * 获取主键或者服刑人员编号
     * @param boundSql boundSql
     * @param parameterObject parameterObject
     * @param configuration configuration
     * @return Map<String,String>
     */
    public static Map<String,String> getIdOrPriNum(BoundSql boundSql,Object parameterObject,Configuration configuration) {
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        Map<String,String> map = new HashMap<>(16);
        if(null != parameterMappings) {
            for(ParameterMapping parameterMapping : parameterMappings) {
                Object value = null;
                if(parameterMapping.getMode() != ParameterMode.OUT) {
                    String propertyName = parameterMapping.getProperty();
                        if(propertyName.contains("prinum") ||
                            propertyName.contains("priNum") ||
                            propertyName.contains("singleid") ||
                            propertyName.contains("sessionid") ||
                            propertyName.contains("pp") ||
                            propertyName.contains("id")) {
                        if(boundSql.hasAdditionalParameter(propertyName)) {
                            value = boundSql.getAdditionalParameter(propertyName);
                        } else if(null == parameterObject) {
                        } else if(typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                            value = parameterObject;
                        } else {
                            MetaObject metaObject = configuration.newMetaObject(parameterObject);
                            value = metaObject.getValue(propertyName);
                        }
                    }
                    if(null != value) {
                        String[] split = propertyName.toLowerCase().split("\\.");
                        // 当SQL语句有多个参数的时候
                        if(2 == split.length) {
                            map.put(propertyName.toLowerCase().split("\\.")[1],String.valueOf(value));
                        } else {
                            map.put(propertyName.toLowerCase(),String.valueOf(value));
                        }
                    }
                }
            }
        }
        return map;
    }

    /**
     * 描述
     * @param idMap idMap
     * @param template template
     * @param sql sql
     * @return Map<String,String>
     */
    public static Map<String,String> determinePrinum(Map<String,String> idMap, JdbcTemplate template, String sql) {
        String prinum = "";
        Map<String,String> resultMap = new HashMap<>(16);
        // 默认为false
        resultMap.put("flag","false");
        // 罪犯编号
        if(null != idMap.get("prinum") || null != idMap.get("pid")) {
            prinum = null != idMap.get("prinum") ? idMap.get("prinum") : idMap.get("pid");
            if(16 == prinum.length() || prinum.contains(Constants.OldDataFlag.A_FLAG) || prinum.contains(Constants.OldDataFlag.B_FLAG)) {
                resultMap.put("flag","true");
                resultMap.put("prinum",prinum);
                return resultMap;
            } else {
                resultMap.put("flag","false");
            }
            // 主键ID
        } else if(null != idMap.get("id") || null != idMap.get("singleid")) {
            String id = idMap.get("id");
            String tempSql = "";
            if(sql.toLowerCase().contains("prisoner_main")) {
                tempSql = "select pri_num from prisoner_main where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisave_temp")) {
                tempSql = "select pri_num from prisave_temp where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_zz")) {
                tempSql = "select pri_num from prisoner_zz where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_fx")) {
                tempSql = "select pri_num from prisoner_fx where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_sr")) {
                tempSql = "select pri_num from prisoner_sr where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_zn")) {
                tempSql = "select pri_num from prisoner_zn where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_hc")) {
                tempSql = "select pri_num from prisoner_hc where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoners_zyjn")) {
                tempSql = "select pri_num from prisoners_zyjn where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoners_jxjs")) {
                tempSql = "select pri_num from prisoners_jxjs where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_cjpg")) {
                tempSql = "select pri_num from prisoner_cjpg where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_xjxx")) {
                tempSql = "select pri_num from prisoner_xjxx where id = '" + id + "'";
            } else if(sql.toLowerCase()
                    .contains("ab_prisoners") && " ".equals(String.valueOf(sql.charAt(sql.toLowerCase()
                    .indexOf("ab_prisoners") + "ab_prisoners".length())))) {
                tempSql = "select pri_num from ab_prisoners where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_xiaozu")) {
                tempSql = "select pri_num from ab_xiaozu where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_mv")) {
                tempSql = "select pri_num from ab_mv where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("upload_appendix_file") && null != idMap
                    // 上传文件功能表的唯一标识
                    .get("singleid")) {
                String singleid = idMap.get("singleid");
                tempSql = "select pri_num from upload_appendix_file where single_id = '" + singleid + "'";
            } else if(sql.toLowerCase().contains("ab_tuoguan")) {
                tempSql = "select pri_num from ab_tuoguan where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_anzhi")) {
                tempSql = "select pri_num from ab_anzhi where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_jiu")) {
                tempSql = "select pri_num from ab_jiu where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_bangfu")) {
                tempSql = "select pri_num from ab_bangfu where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_genzongbangjiao")) {
                tempSql = "select pri_num from ab_genzongbangjiao where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_zoufang")) {
                tempSql = "select pri_num from ab_zoufang where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_shehuijiuzhu")) {
                tempSql = "select pri_num from ab_shehuijiuzhu where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_zyzjiuzhu")) {
                tempSql = "select pri_num from ab_zyzjiuzhu where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_paicha")) {
                tempSql = "select pri_num from ab_paicha where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_cun_jianding")) {
                tempSql = "select pri_num from ab_cun_jianding where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_lieguan")) {
                tempSql = "select pri_num from ab_lieguan where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_prisoners_jcab")) {
                tempSql = "select pri_num from ab_prisoners_jcab where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("prisoner_transfer")) {
                tempSql = "select pri_num from prisoner_transfer where id = '" + id + "'";
            } else if(sql.toLowerCase().contains("ab_zoufang")) {
                tempSql = "select pri_num from ab_zoufang where id = '" + id + "'";
            }
            if(!"".equals(tempSql)) {
                try {
                    prinum = template.queryForObject(tempSql,String.class);
                } catch(DataAccessException e) {
                    prinum = "";
                }
            }
            assert prinum != null;
            if(16 == prinum.length()) {
                resultMap.put("flag","true");
                resultMap.put("prinum",prinum);
                return resultMap;
            } else {
                resultMap.put("flag","false");
            }
        } else if(null != idMap.get("pp")) {
            String pp = idMap.get("pp");
            String tempSql = "";
            if(sql.toLowerCase().contains("ab_bangfu")) {
                tempSql = "select pri_num from ab_bangfu where pp = '" + pp + "'";
            } else if(sql.toLowerCase().contains("ab_genzongbangjiao")) {
                tempSql = "select pri_num from ab_genzongbangjiao where pp = '" + pp + "'";
            } else if(sql.toLowerCase().contains("ab_shehuijiuzhu")) {
                tempSql = "select pri_num from ab_shehuijiuzhu where pp = '" + pp + "'";
            } else if(sql.toLowerCase().contains("ab_zyzjiuzhu")) {
                tempSql = "select pri_num from ab_zyzjiuzhu where pp = '" + pp + "'";
            } else if(sql.toLowerCase().contains("ab_paicha")) {
                tempSql = "select pri_num from ab_paicha where pp = '" + pp + "'";
            }
            try {
                prinum = template.queryForObject(tempSql,String.class);
            } catch(DataAccessException e) {
                prinum = "";
            }
            assert prinum != null;
            if(16 == prinum.length()) {
                resultMap.put("flag","true");
                resultMap.put("prinum",prinum);
                return resultMap;
            } else {
                resultMap.put("flag","false");
            }
        } else if(null != idMap.get("sessionid")) {
            String sessionid = idMap.get("sessionid");
            String tempSql = "";
            if(sql.toLowerCase().contains("xj_receipt_data")) {
                tempSql = "select pri_num from xj_receipt where xjid = '" + sessionid + "'";
            }
            try {
                prinum = template.queryForObject(tempSql,String.class);
            } catch(DataAccessException e) {
                prinum = "";
            }
            assert prinum != null;
            if(16 == prinum.length()) {
                resultMap.put("flag","true");
            } else {
                resultMap.put("flag","false");
            }
        }
        //校验sql中是否包含下面的表
        boolean flag = StrUtil.contains(sql,"org_staff","bjgz_ry","xtjc_sxt","azbj_jdgl","jyxx_mk","jypx_jg","zfbz_xxgl","shgyzz_xxgl","zyz_xxgl");
        if(flag) {
            resultMap.put("flag","false");
        }
        resultMap.put("prinum",prinum);
        return resultMap;
    }

    /**
     * 替换sql
     * @param sql sql
     * @param map map
     * @return String
     */
    public static String replace(String sql,Map<String,Object> map) {
        for(Map.Entry<String,Object> entry : map.entrySet()) {
            if(sql.contains("?" + entry.getKey())) {
                sql = sql.replace("?" + entry.getKey(),entry.getValue().toString());
            }
        }
        return sql;
    }
}